import os.path

from PySide2.QtWidgets import QMessageBox
# import pandas as pd
from pandas import read_excel, ExcelWriter


class ExcelFindUserServer:
    """
    excel 查找用户业务类
    """
    def __init__(self,excel_path:str):
        self.result = None
        self.df = read_excel(excel_path,sheet_name=None,engine='openpyxl')

    def find_user(self,ids=[]):
        """
        查询用户
        :param ids: 用户集合
        :return: dataframe对象
        """
        id_names = ["//样本ID", "SID"]
        # 获取所有的sheet
        temp = self.df.copy()
        keys = temp.keys()
        for key in keys:
            tempdf = temp[key]
            id_list = list(set(tempdf.keys()) & set(id_names))
            if len(id_list) == 1:
                # 有id
                # 输出过滤后的数据
                temp[key] = tempdf[tempdf[id_list[0]].isin(ids)]
                temp[key] = temp[key].reset_index(drop=True)
        self.result = temp
        return temp
    def to_excel(self,excel_path,file_name="过滤后"):
        """
        输出过滤后的数据
        :param excel_path: 输出路径
        :param file_name: 输出名称
        :return:
        """
        if not self.result is None:
            # 输出到指定路径
            with ExcelWriter(f"{excel_path}/{file_name}.xlsx") as writer:
                for key in self.result:
                    if len(self.result[key]) == 0:
                        continue
                    self.result[key].to_excel(writer, sheet_name=key, index=False)
            QMessageBox.information(None, '提示', '导出完成')


    def find_user_to_excel(self,ids=[]):
        if not os.path.exists("./输出结果/excel查找"):
            os.makedirs("./输出结果/excel查找")
        with ExcelWriter("./输出结果/excel查找/过滤后.xlsx") as writer1:
            with ExcelWriter("./输出结果/excel查找/剩余数据.xlsx") as writer2:
                for key in self.df.keys():
                    tempdf = self.df[key]
                    id_list = list(set(tempdf.keys()) & set(ids))
                    if len(id_list) == 1:
                        # 有id
                        # 输出过滤后的数据
                        temp = tempdf[tempdf[id_list[0]].isin(ids)]
                        if temp.shape[0] == 0:
                            continue
                        tempdf[tempdf[id_list[0]].isin(ids)].to_excel(writer1, sheet_name=key, index=False)
                        # 输出剩余数据
                        tempdf[~tempdf[id_list[0]].isin(ids)].to_excel(writer2, sheet_name=key, index=False)
                    else:
                        # 没有 或者有多个直接不执行
                        continue
        writer1.close()
        writer2.close()

    def find_user_one_to_excel(self,file_name="过滤后",ids=[]):
        """
        输出查找的数据
        :param ids: 要查找的数据列表
        :return: None
        """
        id_names = ["//样本ID", "SID"]
        if not os.path.exists("./结果输出/excel查找"):
            os.makedirs("./结果输出/excel查找")
        with ExcelWriter(f"./结果输出/excel查找/{file_name}.xlsx") as writer1:
                for key in self.df.keys():
                    tempdf = self.df[key]
                    id_list = list(set(tempdf.keys()) & set(id_names))
                    if len(id_list) == 1:
                        # 有id
                        # 输出过滤后的数据
                        temp = tempdf[tempdf[id_list[0]].isin(ids)]
                        if temp.shape[0] == 0:
                            continue
                        tempdf[tempdf[id_list[0]].isin(ids)].to_excel(writer1, sheet_name=key, index=False)
                    else:
                        # 没有 或者有多个直接不执行

                        continue

